package com.kt.dao.imp;

import com.kt.dao.ProductDAO;
import com.kt.domain.Order;
import com.kt.domain.Product;
import com.kt.utils.UtilDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImp implements ProductDAO {

    private Product formatProduct(ResultSet resultSet) {
        Product product = null;
        try {
            product = new Product();
            product.setPname(resultSet.getString("pName"));
            product.setPrice(resultSet.getDouble("price"));
            product.setPid(resultSet.getInt("pid"));
            product.setBrand(resultSet.getString("brand"));
            product.setPicture(resultSet.getString("picture"));
            product.setDesc(resultSet.getString("pDesc"));
            product.setCid(resultSet.getInt("cid"));
            product.setUid(resultSet.getInt("uid"));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return product;
    }

    public List<Product> findAllProducts(Integer firstPid) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where pid > ? and pid < ?";
        ResultSet resultSet = null;
        List<Product> list = new ArrayList<Product>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, firstPid);
            preparedStatement.setInt(2, firstPid+20);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {

                list.add(formatProduct(resultSet));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }
        return list;
    }

    public List<Product> findProductByUid(int uid) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where uid = ?";
        ResultSet resultSet = null;
        List<Product> list = new ArrayList<Product>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {

                list.add(formatProduct(resultSet));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }
        return list;
    }

    public List<Product> findProductByBrand(String brand) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where brand = ?";
        ResultSet resultSet = null;
        List<Product> list = new ArrayList<Product>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, brand);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {

                list.add(formatProduct(resultSet));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }
        return list;
    }

    public List<Product> findProductByCid(int cid) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where cid = ?";
        ResultSet resultSet = null;
        List<Product> list = new ArrayList<Product>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, cid);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {

                list.add(formatProduct(resultSet));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }
        return list;
    }

    @Override
    public List<Product> findProductByKewWord(String kw) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        kw = "'%" + kw + "%'";
        String sql = "select * from product where pName like ?";
        ResultSet resultSet = null;
        List<Product> list = new ArrayList<Product>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, kw);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {

                list.add(formatProduct(resultSet));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }
        return list;
    }

    public Product findByName(String name) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where pname = ?";
        Product product = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                product = formatProduct(resultSet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }

        return product;
    }

    public Product findById(String id) {
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select * from product where pid = ?";
        Product product = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                product = formatProduct(resultSet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            UtilDB.close(connection);
        }

        return product;
    }

    public int delete(String name) {
        int result = 0;
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "delete from product where pname = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            result = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }

    public int addProduct(Product product) {
        int result = 0;
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "insert into product values (null, ?, ?, ?, ?, ?, ?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, product.getPname());
            preparedStatement.setDouble(2, product.getPrice());
            preparedStatement.setString(3, product.getPicture());
            preparedStatement.setString(4, product.getDesc());
            preparedStatement.setString(5, product.getBrand());
            preparedStatement.setInt(6, product.getCid());
            result = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }

    public int changeProductInfo(Product product) {
        int result = 0;
        Connection connection = UtilDB.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update product set pName=?, price=?, picture=?, pDesc=?, brand=?, cid=? where pid = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, product.getPname());
            preparedStatement.setDouble(2, product.getPrice());
            preparedStatement.setString(3, product.getPicture());
            preparedStatement.setString(4, product.getDesc());
            preparedStatement.setString(5, product.getBrand());
            preparedStatement.setInt(6, product.getCid());
            preparedStatement.setInt(7, product.getPid());

            result = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }
}
